import openpyxl


def index_convert(target):
    """
    将字母列替换为下标列
    """
    target = str(target).lower()
    index = 0
    if len(target) > 1:
        index = (ord(target[0]) - 96) * 26 + ord(target[1]) - 97
    else:
        index = ord(target) - 97
    return index


def get_dict(file_path, sheet_name):
    """
    从Excel映射文件获取下标字典
    """
    print('-----------------开始创建列下标字典')
    # 读取excel
    ws = openpyxl.load_workbook(file_path, read_only=True)[sheet_name]
    index_map = {}
    # 解析下标字典
    for row in ws.rows:
        if row[2].value is None:
            continue
        index_map[row[1].value] = index_convert(row[2].value)

    for key, value in index_map.items():
        print(f'{key}: {value}')

    print('-----------------列下标字典创建完成！')
    return index_map


class CellDataMap:
    def __init__(self, index, value):
        self.index = index
        self.value = value

    def __str__(self):
        return f'CellDataMap(index={self.index}, value={self.value})'
        # return str(f'1111111111111111111')


class CellMap:
    """
    单元对象
    """

    def __init__(self, index_dict):
        # 基站名称
        self.node_name = CellDataMap(None, None)
        # 地市
        self.city = CellDataMap(None, None)
        # 日期
        self.time = CellDataMap(None, None)
        # 基站标识
        self.node_id = CellDataMap(None, None)
        # 小区ID
        self.cell_id = CellDataMap(None, None)
        # 小区双工模式
        self.cell_duplex = CellDataMap(None, None)
        # 小区名称
        self.cell_name = CellDataMap(None, None)
        # 本地小区标识
        self.bendi_cell_id = CellDataMap(None, None)
        # 完整度
        self.integrity = CellDataMap(None, None)
        # 小区下行吞吐量（兆字节）
        self.cell_down_throughput = CellDataMap(None, None)
        # 小区上行吞吐量（兆字节）
        self.cell_up_throughput = CellDataMap(None, None)
        # 小区最大用户数
        self.cell_max_ue = CellDataMap(None, None)
        # RRC连接建立完成次数
        self.rrc_connect_complete_count = CellDataMap(None, None)
        # RRC连接请求次数（不包括重发）
        self.rrc_connect_all_count = CellDataMap(None, None)
        # E-RAB建立成功总次数
        self.e_rab_success_count = CellDataMap(None, None)
        # E-RAB建立尝试总次数
        self.e_rab_all_count = CellDataMap(None, None)
        # E-RAB异常释放总次数
        self.e_rab_release_err_count = CellDataMap(None, None)
        # E-RAB正常释放总次数
        self.e_rab_release_success_count = CellDataMap(None, None)
        # eNodeB内同频切换出成功次数
        self.in_node_same_frequency_change_success_count = CellDataMap(None, None)
        # eNodeB内同频切换出执行次数
        self.in_node_same_frequency_change_all_count = CellDataMap(None, None)
        # eNodeB间同频切换出成功次数
        self.between_node_same_frequency_change_success_count = CellDataMap(None, None)
        # eNodeB间同频切换出执行次数
        self.between_node_same_frequency_change_all_count = CellDataMap(None, None)
        # 无线资源不足导致E-RAB建立失败次数
        self.e_rab_low_resource_err_count = CellDataMap(None, None)
        # 系统上行每个PRB上检测到的干扰噪声的平均值(毫瓦分贝)
        self.system_up_prb_interference_noise_average = CellDataMap(None, None)
        # 系统上行每个PRB上检测到的干扰噪声的最大值(毫瓦分贝)
        self.system_up_prb_interference_noise_max = CellDataMap(None, None)
        # 系统上行每个PRB上检测到的干扰噪声的最小值(毫瓦分贝)
        self.system_up_prb_interference_noise_min = CellDataMap(None, None)
        # 下行PRB被使用的平均个数
        self.system_down_used_prb_average_count = CellDataMap(None, None)
        # 下行可用的PRB个数
        self.system_down_all_prb_count = CellDataMap(None, None)
        # E-UTRAN向WCDMA执行的CSFB重定向次数
        self.e_utran_to_wcdma_csfb_redirect_count = CellDataMap(None, None)
        # E-UTRAN向WCDMA执行的CSFB切换执行成功次数
        self.e_utran_to_wcdma_csfb_change_success_count = CellDataMap(None, None)
        # eNodeB收到的CSFB触发次数
        self.node_csfb_trigger_count = CellDataMap(None, None)
        # 重定向到WCDMA的总次数
        self.redirect_to_cdma_all_count = CellDataMap(None, None)
        # 小区内的平均用户数
        self.cell_average_ue = CellDataMap(None, None)
        # 覆盖触发的重定向到WCDMA的总次数
        self.cover_trigger_redirect_to_wcdma_all_count = CellDataMap(None, None)
        # E-RAB建立平均时长(毫秒)
        self.e_rab_establish_average_time = CellDataMap(None, None)
        # CQI 0数量
        self.cqi_0_count = CellDataMap(None, None)
        # CQI 1数量
        self.cqi_1_count = CellDataMap(None, None)
        # CQI 2数量
        self.cqi_2_count = CellDataMap(None, None)
        # CQI 3数量
        self.cqi_3_count = CellDataMap(None, None)
        # CQI 4数量
        self.cqi_4_count = CellDataMap(None, None)
        # CQI 5数量
        self.cqi_5_count = CellDataMap(None, None)
        # CQI 6数量
        self.cqi_6_count = CellDataMap(None, None)
        # CQI 7数量
        self.cqi_7_count = CellDataMap(None, None)
        # CQI 8数量
        self.cqi_8_count = CellDataMap(None, None)
        # CQI 9数量
        self.cqi_9_count = CellDataMap(None, None)
        # CQI 10数量
        self.cqi_10_count = CellDataMap(None, None)
        # CQI 11数量
        self.cqi_11_count = CellDataMap(None, None)
        # CQI 12数量
        self.cqi_12_count = CellDataMap(None, None)
        # CQI 13数量
        self.cqi_13_count = CellDataMap(None, None)
        # CQI 14数量
        self.cqi_14_count = CellDataMap(None, None)
        # CQI 15数量
        self.cqi_15_count = CellDataMap(None, None)

        # 初始化
        self.set_dict(index_dict)

    def set_dict(self, index_dict):
        """
        从字典获取列下标
        :param index_dict:列下标字典
        """
        for key in index_dict.keys():
            self[key].index = index_dict[key]

    def set_row_map(self, row):
        """
        行数据映射
        :param row: 一行数据
        """
        for key in self.__dict__.keys():
            if self[key].index is None:
                continue
            self[key].value = row[self[key].index].value
        # print(f'-----------------行数据映射完成！')

    def __getitem__(self, item):
        return getattr(self, item)

    def __str__(self):
        return f'基站名称：{self.node_name} | 地市：{self.city}'
